﻿/******************************************************/
// DALOrder データアクセスクラス  
/******************************************************/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using MAA.Entity;

namespace MAA.Data
{
    /// <summary>
    /// Process data access to database refer to order
    /// </summary>
    public class DALOrder
    {
        private string conStr = ConfigurationManager.AppSettings["connectionString"];

        /// <summary>
        /// Get all orders of an user
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public DataTable getOrder(string lang, int pageIndex, int pageSize, long userID, int riceID, short provinceID, short statusID, out long rowNum)
        {
            DataTable tblOrder = new DataTable();
            rowNum = 0;

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd;
            cmd = new SqlCommand("GetTransactionOrder", conn);

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@Lang", SqlDbType.VarChar).Value = lang;
            cmd.Parameters.Add("@PageIndex", SqlDbType.Int).Value = pageIndex;
            cmd.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;
            cmd.Parameters.Add("@userID", SqlDbType.BigInt).Value = userID;
            cmd.Parameters.Add("@riceID", SqlDbType.Int).Value = riceID;
            cmd.Parameters.Add("@provinceID", SqlDbType.TinyInt).Value = provinceID;
            cmd.Parameters.Add("@statusID", SqlDbType.TinyInt).Value = statusID;

            cmd.Parameters.Add("@RecordCount", SqlDbType.BigInt);
            cmd.Parameters["@RecordCount"].Direction = ParameterDirection.Output;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblOrder);
            rowNum = Convert.ToInt64(cmd.Parameters["@RecordCount"].Value);
            conn.Close();

            return tblOrder;
        }

        /// <summary>
        /// Get detail information of a order
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public DataTable getOrderInfo(long orderID)
        {
            DataTable orderDataTable = new DataTable();
            SqlDataAdapter orderAdapter = new SqlDataAdapter();
            orderAdapter.SelectCommand = new SqlCommand();

            orderAdapter.SelectCommand.CommandText = @"
                SELECT		[Order].Amount,
			                [Order].Price,
			                [Order].CreateDate,
			                [Order].[Status],
			                CONVERT(varchar, [Order].CreateDate, 105) AS UpdateTime
                FROM		[Order]
                WHERE		[Order].ID = @orderID";

            orderAdapter.SelectCommand.Parameters.Add("@orderID", SqlDbType.BigInt).Value = orderID;

            orderAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            orderAdapter.Fill(orderDataTable);
            orderAdapter.SelectCommand.Connection.Close();

            return orderDataTable;
        }

        /// <summary>
        /// Get list of buyers and orders correspond to a post
        /// </summary>
        /// <param name="postID"></param>
        /// <returns></returns>
        public DataTable getOrderPost(string lang, long postID)
        {
            DataTable orderBuyerDataTable = new DataTable();
            SqlDataAdapter orderBuyerAdapter = new SqlDataAdapter();
            orderBuyerAdapter.SelectCommand = new SqlCommand();

            if (lang.Equals("vi"))
            {
                orderBuyerAdapter.SelectCommand.CommandText = @"
                SELECT		[User].ID AS UserID,
			                [User].Name,
			                [User].Phone,
			                [User].Email,
			                [User].[Address],
			                Province.Name AS Province,
			                [Order].ID AS OrderID,
			                [Order].Amount,
			                [Order].Price,
			                [Order].[Status],
                            CONVERT(varchar, [Order].CreateDate, 105) AS CreateDate
                FROM		[User], [Order], Province
                WHERE		[User].ID = [Order].UserID
					AND		Province.ID = [User].ProvinceID
	                AND		[Order].PostID = @postID
                ORDER BY    [Order].CreateDate";
            }
            else
            {
                orderBuyerAdapter.SelectCommand.CommandText = @"
                SELECT		[User].ID AS UserID,
			                [User].Name,
			                [User].Phone,
			                [User].Email,
			                [User].[Address],
			                Province.NameJP AS Province,
			                [Order].ID AS OrderID,
			                [Order].Amount,
			                [Order].Price,
			                [Order].[Status],
                            CONVERT(varchar, [Order].CreateDate, 105) AS CreateDate
                FROM		[User], [Order], Province
                WHERE		[User].ID = [Order].UserID
					AND		Province.ID = [User].ProvinceID
	                AND		[Order].PostID = @postID
                ORDER BY    [Order].CreateDate";
            }
            orderBuyerAdapter.SelectCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;

            orderBuyerAdapter.SelectCommand.Connection = new SqlConnection(conStr);
            orderBuyerAdapter.Fill(orderBuyerDataTable);
            orderBuyerAdapter.SelectCommand.Connection.Close();

            return orderBuyerDataTable;
        }

        /// <summary>
        /// Add a new order
        /// </summary>
        /// <param name="order"></param>
        /// <returns></returns>
        public void insertOrder(Order order)
        {
            SqlDataAdapter orderAdapter = new SqlDataAdapter();
            orderAdapter.InsertCommand = new SqlCommand();

            orderAdapter.InsertCommand.CommandText = @"
                INSERT INTO [Order](				
				            UserID, 
				            PostID, 
				            Amount, 
				            Price, 
				            CreateDate, 
				            [Status])
                VALUES		(
                            @userID, 	
			                @postID, 	
			                @amount, 	
			                @price, 	
			                @date, 	
			                @status)";

            orderAdapter.InsertCommand.Parameters.Add("@userID", SqlDbType.BigInt).Value = order.UserID;
            orderAdapter.InsertCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = order.PostID;
            orderAdapter.InsertCommand.Parameters.Add("@amount", SqlDbType.BigInt).Value = order.Amount;
            orderAdapter.InsertCommand.Parameters.Add("@price", SqlDbType.Int).Value = order.Price;
            orderAdapter.InsertCommand.Parameters.Add("@date", SqlDbType.Date).Value = order.Date;
            orderAdapter.InsertCommand.Parameters.Add("@status", SqlDbType.TinyInt).Value = order.Status;

            orderAdapter.InsertCommand.Connection = new SqlConnection(conStr);
            orderAdapter.InsertCommand.Connection.Open();
            orderAdapter.InsertCommand.ExecuteNonQuery();
            orderAdapter.InsertCommand.Connection.Close();
        }

        /// <summary>
        /// Update status of an order
        /// </summary>
        /// <param name="orderID"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public void updateOrder(long orderID, short status)
        {
            SqlDataAdapter orderAdapter = new SqlDataAdapter();
            orderAdapter.UpdateCommand = new SqlCommand();

            orderAdapter.UpdateCommand.CommandText = @"
                UPDATE		[Order]
                SET			[Order].[Status] = @status
                WHERE		[Order].ID = @orderID";

            orderAdapter.UpdateCommand.Parameters.Add("@orderID", SqlDbType.BigInt).Value = orderID;
            orderAdapter.UpdateCommand.Parameters.Add("@status", SqlDbType.SmallInt).Value = status;

            orderAdapter.UpdateCommand.Connection = new SqlConnection(conStr);
            orderAdapter.UpdateCommand.Connection.Open();
            orderAdapter.UpdateCommand.ExecuteNonQuery();
            orderAdapter.UpdateCommand.Connection.Close();
        }

        /// <summary>
        /// Delete a order
        /// </summary>
        /// <param name="orderID"></param>
        /// <returns></returns>
        public void deleteOrder(long orderID)
        {
            SqlDataAdapter orderAdapter = new SqlDataAdapter();
            orderAdapter.DeleteCommand = new SqlCommand();

            orderAdapter.DeleteCommand.CommandText = @"
                DELETE FROM [Order]			
                WHERE		[Order].ID = @orderID";

            orderAdapter.DeleteCommand.Parameters.Add("@orderID", SqlDbType.BigInt).Value = orderID;

            orderAdapter.DeleteCommand.Connection = new SqlConnection(conStr);
            orderAdapter.DeleteCommand.Connection.Open();
            orderAdapter.DeleteCommand.ExecuteNonQuery();
            orderAdapter.DeleteCommand.Connection.Close();
        }

        /// <summary>
        /// Delete orders of a post by status
        /// </summary>
        /// <param name="postID"></param>
        /// <param name="status"></param>
        public void deleteOrder(long postID, short status)
        {
            SqlDataAdapter orderAdapter = new SqlDataAdapter();
            orderAdapter.DeleteCommand = new SqlCommand();

            orderAdapter.DeleteCommand.CommandText = @"
                DELETE FROM [Order]			
                WHERE		[Order].PostID = @postID
	                AND		[Order].[Status] = @status";

            orderAdapter.DeleteCommand.Parameters.Add("@postID", SqlDbType.BigInt).Value = postID;
            orderAdapter.DeleteCommand.Parameters.Add("@status", SqlDbType.SmallInt).Value = status;

            orderAdapter.DeleteCommand.Connection = new SqlConnection(conStr);
            orderAdapter.DeleteCommand.Connection.Open();
            orderAdapter.DeleteCommand.ExecuteNonQuery();
            orderAdapter.DeleteCommand.Connection.Close();
        }
    }
}